<?php
/**
 * 
 * Utility class to access the database 
 *
 */
class DBUtils
{
	/**
	 * @var ADOConnection
	 */
	protected $connection;

	private static $instance;

	/**
	 * Gets a connection to the DB
	 * @return DBUtils
	 */
	public static function instance()
	{
		if(!DBUtils::$instance)
			DBUtils::$instance = new DBUtils();
			
		return DBUtils::$instance;
	}
	
	public function __construct()
	{
		$this->connection = $this->initConnection();
	}
	
	/**
	 * @return ADOConnection
	 */
	private function initConnection()
	{
		$c = NewADOConnection(DB_TYPE);
		@$res = $c->Connect(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
		
		if(!$res)
			throw new Exception("Unable to connect to the database !");
		
		return $c;
	}
	
	/**
	 * Selects rows into a value-object array
	 * @param string $sql
	 * @param array $parameters
	 * @param boolean $objectsAsAssociativeArrays : true if we prefer an array of associative arrays instead of an array of objects
	 */
	public function selectToVOArray($sql, $parameters = array(), $objectsAsAssociativeArrays = false)
	{
		$rs = $this->connection->Execute($sql, $parameters);
		if(!$rs)
			throw $this->getSqlException();
			
		$arr = $this->rsToVOArray($rs, $objectsAsAssociativeArrays);
		return $arr;
	}
	
	public function selectSingleVO($sql, $parameters = array(), $objectsAsAssociativeArrays = false)
	{
		$arr = $this->selectToVOArray($sql, $parameters, $objectsAsAssociativeArrays);
		if(!empty($arr))
			return $arr[0];
		else
			return null;
	}
	
	public function execute($sql, $parameters = array())
	{
		$rs = $this->connection->Execute($sql, $parameters);

		if(!$rs)
			throw $this->getSqlException();

		return $this->connection->Affected_Rows();
	}
	
	protected function getSqlException()
	{
		return new Exception($this->connection->ErrorNo().": ". $this->connection->ErrorMsg());
	}
	
	/**
	 * Transforms a Recordset into a value object array
	 * 
	 * @param ADORecordSet $rs
	 * @param bool $encodeBase64
	 */
	protected function rsToVOArray($rs, $objectsAsAssociativeArrays = false)
	{
		$arr = array();
		
		while(!$rs->EOF)
		{
			if($objectsAsAssociativeArrays)
				$vo = array();
			else
				$vo = new stdClass();

			foreach($rs->fields as $key => $val)
			{
				if(is_numeric($key))
					continue;
				$key = strtoupper($key);
				$val = utf8_encode($val);
				
				if($objectsAsAssociativeArrays)
					$vo[$key] = $val;
				else
					$vo->$key = $val;
			}
			
			$arr[] = $vo;
			$rs->MoveNext();
		}
		
		return $arr;
	}
	
	public function getId($table, $field)
	{
		$sql = "Select * from ids where tbl = ? and fld = ?";
		$rs = $this->connection->Execute($sql, array($table, $field));
		
		$arr = $this->rsToVOArray($rs);
		if(count($arr) == 0)
		{
			$this->connection->Execute("Insert into ids (tbl, fld, lastId) values (?,?,1)",
										array($table, $field));
			return 1;
		}
		else
		{
			$id = $arr[0]->LASTID + 1;
			$this->connection->Execute("Update ids set lastId = ? where tbl = ? and fld = ?",
										array($id, $table, $field));
			return $id;
		}
	}
	
	public function beginTrans()
	{
		$this->connection->BeginTrans();
	}
	
	public function commitTrans()
	{
		$this->connection->CommitTrans(true);
	}
	
	public function rollbackTrans()
	{
		$this->connection->RollbackTrans();
	}
}
?>